package report

import org.apache.spark.sql.{DataFrame, SQLContext}
import org.apache.spark.{SparkConf, SparkContext}

/**
  * Created by 王康 on 2018/6/29.
  */
object Regional_distribution_SQL {
  def main(args: Array[String]) {
    val conf: SparkConf = new SparkConf().setAppName("地域分布").setMaster("local[4]")
    conf.set("spark.serializer", "org.apache.spark.serializer.KryoSerializer")
    val ssc: SparkContext = new SparkContext(conf)

    val sqlcontext: SQLContext = new SQLContext(ssc)

    val parquet: DataFrame = sqlcontext.read.parquet("E:\\大数据资料\\project2\\parquet_1.6.3")

    parquet.registerTempTable("Regional_distribution")

    val sql: DataFrame = sqlcontext.sql(
      """
        |select provincename,cityname,sum(if (requestmode =1 and processnode>=1,1,0)) as `原始请求数`,
        |sum(if(requestmode =1 and processnode>=2,1,0)) as `有效请求数`,
        |sum(if(requestmode =1 and processnode=3,1,0)) as `广告请求数`,
        |sum(if(iseffective = 1 and isbilling = 1 and isbid = 1 and adorderid !=0,1,0)) as `参与竞价数`,
        |sum(if(iseffective = "1" and isbilling ="1" and iswin = "1" ,1,0)) as `竞价成功数`,
        |sum(if(requestmode = 2 and iseffective = "1",1,0)) as `展示数`,
        |sum(if(requestmode = 3 and iseffective = "1",1,0)) as `点击数`,
        |sum(if(iseffective = "1" and isbilling ="1" and iswin = "1",winprice,0))/1000 as `DSP广告消费`,
        |sum(if(iseffective = "1" and isbilling ="1" and iswin = "1",adpayment,0))/1000 as `DSP广告成本`
        |from Regional_distribution group BY provincename,cityname 
      """.stripMargin)
    sql.coalesce(4).write.json("E:\\大数据资料\\project2\\Regional_distribution_SQL")
    ssc.stop()
  }
}
